﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class LeadDal
    {
        public DapperHelper dapperHelper { get; set; }
        public ILog log { get; set; }


        /// <summary>
        /// 返回转诊患者信息
        /// </summary>
        /// <param name="modifiedOn">本地修改时间</param>
        /// <param name="skipNums">跳过行数</param>
        /// <param name="pageSize">每页大小</param>
        /// <returns></returns>
        public IEnumerable<dynamic> QueryPatientLeadPageByModifiedOn(DateTime modifiedOn,int skipNums,int pageSize)
        {
            string sqlstr = @" select  leadid,l.fullname,l.new_gender,l.new_age,
                                dbo.fn_UTCToTzCodeSpecificLocalTime(l.new_birthdate, 220) as new_birthdate
                                ,la.Telephone1 as address1_telephone1,l.new_citizenid
                                ,la.[Name] as address1_name,l.[description],l.new_patientsource
                                ,l.new_sourceremark,l.new_mxqd,l.new_disease,
                                l.initialcommunication,l.new_popularscience,
                                l.new_datasource,u.FullName as ownerid,
                                dbo.fn_UTCToTzCodeSpecificLocalTime(l.new_inputingtime, 220) as new_inputingtime,
                                dbo.fn_UTCToTzCodeSpecificLocalTime(l.modifiedon, 220) as modifiedon,
                                uc.FullName as createdby,
                                dbo.fn_UTCToTzCodeSpecificLocalTime(l.createdon, 220) as createdon
                                from LeadBase as l
                                inner join LeadAddressBase as la
                                on l.LeadId = la.ParentId
                                inner join SystemUserBase as u on l.OwnerId = u.SystemUserId
                                left join SystemUserBase as uc on l.CreatedBy = uc.SystemUserId /**where**/ /**orderby**/";

          return  dapperHelper.ExecueWithoutTransaction(a => {
              SqlBuilder sqlBuilder = new SqlBuilder();
              var sqlTemplate = sqlBuilder.AddTemplate(sqlstr);
              sqlBuilder.Where(@" la.AddressNumber=1 and (l.new_datasource = 28 and l.new_mxqd = 100000024)");
              sqlBuilder.Where(@" l.ModifiedOn > @modifiedOn",new { modifiedOn = modifiedOn.ToUniversalTime() });
              sqlBuilder.OrderBy(@" l.CreatedOn OFFSET @skipNums ROWS FETCH NEXT @pageSize ROWS ONLY", 
                  new { skipNums= skipNums, pageSize = pageSize });
              return a.Query<dynamic>(sqlTemplate.RawSql, sqlTemplate.Parameters);
          });
        }


        /// <summary>
        /// 根据姓名、手机、身份证查询潜在客户
        /// 查询条件为：姓名、手机、身份证
        /// 原程序 LeadBaseServiceImpl/select
        /// </summary>
        /// <param name="customerName">姓名</param>
        /// <param name="phone">手机</param>
        /// <param name="sfz">身份证</param>
        /// <returns></returns>
        public LeadView QueryLeadInfoByConditions(string customerName, string phone, string sfz)
        {
            LeadView lead = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from leadbase s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(customerName))
                {
                    sqlBuilder.Where(" s.fullname=@fullname ");
                    sqlParams.Add("@fullname", customerName);
                }
                if (!string.IsNullOrWhiteSpace(phone))
                {
                    sqlBuilder.Where(" s.new_phone=@new_phone ");
                    sqlParams.Add("@new_phone", phone);
                }
                if (!string.IsNullOrWhiteSpace(sfz))
                {
                    sqlBuilder.Where(" s.new_citizenid=@new_citizenid ");
                    sqlParams.Add("@new_citizenid", sfz);
                }

                log.Info(string.Format("LeadDal对像中,QueryLeadInfoByConditions ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                lead = a.QueryFirstOrDefault<LeadView>(sqlTemplate.RawSql, sqlParams);
            });

            return lead;
        }




        /// <summary>
        /// 根据姓名、手机、身份证分页查询潜在客户
        /// </summary>
        /// <param name="customerName"></param>
        /// <param name="phone"></param>
        /// <param name="sfz"></param>
        /// <param name="skipNum"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IEnumerable<LeadView> QueryLeadPageByConditions(string customerName, string phone, string sfz, int skipNum, int pageSize)
        {
            skipNum = (skipNum - 1) * (pageSize - 1);
            IEnumerable<LeadView> lead = null;
            SqlBuilder sqlBuilder = new SqlBuilder();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from Lead s /**where**/ /**orderby**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(customerName))
                {
                    sqlBuilder.OrWhere(" s.fullname=@fullname ", new
                    {
                        fullname = customerName
                    });
                }
                if (!string.IsNullOrWhiteSpace(phone))
                {
                    sqlBuilder.OrWhere(" s.new_phone=@new_phone ", new { new_phone = phone });
                }
                if (!string.IsNullOrWhiteSpace(sfz))
                {
                    sqlBuilder.OrWhere(" s.new_citizenid=@new_citizenid ", new { new_citizenid = sfz });
                }
                sqlBuilder.OrderBy(" createdon asc  OFFSET @skipNum ROW FETCH NEXT @pageSize rows only", new { skipNum = skipNum, pageSize = pageSize });

                log.Info(string.Format("LeadDal对像中,QueryLeadPageByConditions ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, ((DynamicParameters)sqlTemplate.Parameters).ParametersToString(sqlTemplate.RawSql)));
                lead = a.Query<LeadView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });

            return lead;
        }

        public IEnumerable<LeadView> QueryLeadPageByConditions4stat(string customerName, string phone, string sfz, int skipNum, int pageSize, Guid ownerid,Guid screenteamid,DateTime startdate,DateTime enddate,int type)
        {
            skipNum = (skipNum - 1) * (pageSize - 1);
            IEnumerable<LeadView> lead = null;
            SqlBuilder sqlBuilder = new SqlBuilder();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select leadId,fullName,new_gender,new_phone,new_age,new_address,new_insurancetype,new_citizenid,'' as address1_Name from Leadbase s /**where**/ /**orderby**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                //sqlBuilder.Where(" s.ownerid=@ownerid ", new { ownerid = ownerid });

                if (type == 1)
                {
                    sqlBuilder.Where(" s.leadid in(select new_lead from new_reservation where new_screen in (select distinct new_screenId from AIER_SCREEN_INFO where new_screenteamid=@screenteamid) and CreatedOn >= @startdate and CreatedOn <= @enddate)", new { screenteamid = screenteamid, startdate = startdate, enddate = enddate });
                }
                else if (type == 2)
                {
                    sqlBuilder.Where(" s.leadid in(select new_lead from new_reservation where new_screen in (select distinct new_screenId from AIER_SCREEN_INFO where new_screenteamid=@screenteamid) and new_istohospital=1 and CreatedOn >= @startdate and CreatedOn <= @enddate)", new { screenteamid = screenteamid, startdate = startdate, enddate = enddate });
                }
                else if (type == 3)
                {
                    sqlBuilder.Where(" s.leadid in(select new_lead from new_reservation where new_screen in (select distinct new_screenId from AIER_SCREEN_INFO where new_screenteamid=@screenteamid) and new_issign=1 and CreatedOn >= @startdate and CreatedOn <= @enddate)", new { screenteamid = screenteamid, startdate = startdate, enddate = enddate });
                }
                else if (type == 4) {
                    sqlBuilder.Where(" s.leadid in(select new_lead from new_reservation where new_screen in (select distinct new_screenId from AIER_SCREEN_INFO where new_screenteamid=@screenteamid) and new_isoperate=1 and CreatedOn >= @startdate and CreatedOn <= @enddate)", new { screenteamid = screenteamid, startdate = startdate, enddate = enddate });
                }
                else if (type == 5)
                {
                    sqlBuilder.Where(" s.leadid in(select new_lead from new_reservation where new_screen in (select distinct new_screenId from AIER_SCREEN_INFO where new_screenteamid=@screenteamid) and new_isoperate=0 and CreatedOn >= @startdate and CreatedOn <= @enddate)", new { screenteamid = screenteamid, startdate = startdate, enddate = enddate });
                }


                if (!string.IsNullOrWhiteSpace(customerName))
                {
                    sqlBuilder.OrWhere(" s.fullname=@fullname ", new
                    {
                        fullname = customerName
                    });
                }
                if (!string.IsNullOrWhiteSpace(phone))
                {
                    sqlBuilder.OrWhere(" s.new_phone=@new_phone ", new { new_phone = phone });
                }
                if (!string.IsNullOrWhiteSpace(sfz))
                {
                    sqlBuilder.OrWhere(" s.new_citizenid=@new_citizenid ", new { new_citizenid = sfz });
                }
                sqlBuilder.OrderBy(" createdon asc  OFFSET @skipNum ROW FETCH NEXT @pageSize rows only", new { skipNum = skipNum, pageSize = pageSize });

                log.Info(string.Format("LeadDal对像中,QueryLeadPageByConditions ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, ((DynamicParameters)sqlTemplate.Parameters).ParametersToString(sqlTemplate.RawSql)));
                lead = a.Query<LeadView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });

            return lead;
        }

        public IEnumerable<LeadView> QueryLeadPageByConditions(string customerName, string phone, string sfz, int skipNum, int pageSize,Guid ownerid)
        {
            skipNum = (skipNum - 1) * (pageSize - 1);
            IEnumerable<LeadView> lead = null;
            SqlBuilder sqlBuilder = new SqlBuilder();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from Lead s /**where**/ /**orderby**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                
                sqlBuilder.Where(" s.ownerid=@ownerid ", new { ownerid = ownerid });

                if (!string.IsNullOrWhiteSpace(customerName))
                {
                    sqlBuilder.OrWhere(" s.fullname=@fullname ", new
                    {
                        fullname = customerName
                    });
                }
                if (!string.IsNullOrWhiteSpace(phone))
                {
                    sqlBuilder.OrWhere(" s.new_phone=@new_phone ", new { new_phone = phone });
                }
                if (!string.IsNullOrWhiteSpace(sfz))
                {
                    sqlBuilder.OrWhere(" s.new_citizenid=@new_citizenid ", new { new_citizenid = sfz });
                }
                sqlBuilder.OrderBy(" createdon asc  OFFSET @skipNum ROW FETCH NEXT @pageSize rows only", new { skipNum = skipNum, pageSize = pageSize });

                log.Info(string.Format("LeadDal对像中,QueryLeadPageByConditions ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, ((DynamicParameters)sqlTemplate.Parameters).ParametersToString(sqlTemplate.RawSql)));
                lead = a.Query<LeadView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });

            return lead;
        }


        /// <summary>
        /// 根据姓名、手机、身份证查询潜在客户条数
        /// </summary>
        /// <param name="customerName"></param>
        /// <param name="phone"></param>
        /// <param name="sfz"></param>
        /// <returns></returns>
        public int QueryLeadCountByConditions(string customerName, string phone, string sfz)
        {
            int leadcount = 0;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select count(*) from leadbase s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(customerName))
                {
                    sqlBuilder.OrWhere(" s.fullname=@fullname ");
                    sqlParams.Add("@fullname", customerName);
                }
                if (!string.IsNullOrWhiteSpace(phone))
                {
                    sqlBuilder.OrWhere(" s.new_phone=@new_phone ");
                    sqlParams.Add("@new_phone", phone);
                }
                if (!string.IsNullOrWhiteSpace(sfz))
                {
                    sqlBuilder.OrWhere(" s.new_citizenid=@new_citizenid ");
                    sqlParams.Add("@new_citizenid", sfz);
                }

                log.Info(string.Format("LeadDal对像中,QueryLeadPageByConditions ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                leadcount = a.ExecuteScalar<int>(sqlTemplate.RawSql, sqlParams);
            });

            return leadcount;
        }

        public int QueryLeadCountByConditions4stat(string customerName, string phone, string sfz, Guid ownerid,Guid screenteamid,DateTime startdate,DateTime enddate,int type)
        {
            int leadcount = 0;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select count(*) from leadbase s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                //sqlBuilder.Where(" s.ownerid=@ownerid ");
                //sqlParams.Add("@ownerid", ownerid);

                if (type == 1)
                {
                    sqlBuilder.Where(" s.leadid in(select new_lead from new_reservation where new_screen in (select distinct new_screenId from AIER_SCREEN_INFO where new_screenteamid=@screenteamid) and CreatedOn >= @startdate and CreatedOn <= @enddate)", new { screenteamid = screenteamid, startdate = startdate, enddate = enddate });
                }
                else if (type == 2)
                {
                    sqlBuilder.Where(" s.leadid in(select new_lead from new_reservation where new_screen in (select distinct new_screenId from AIER_SCREEN_INFO where new_screenteamid=@screenteamid) and new_istohospital=1 and CreatedOn >= @startdate and CreatedOn <= @enddate)", new { screenteamid = screenteamid, startdate = startdate, enddate = enddate });
                }
                else if (type == 3)
                {
                    sqlBuilder.Where(" s.leadid in(select new_lead from new_reservation where new_screen in (select distinct new_screenId from AIER_SCREEN_INFO where new_screenteamid=@screenteamid) and new_issign=1 and CreatedOn >= @startdate and CreatedOn <= @enddate)", new { screenteamid = screenteamid, startdate = startdate, enddate = enddate });
                }
                else if (type == 4)
                {
                    sqlBuilder.Where(" s.leadid in(select new_lead from new_reservation where new_screen in (select distinct new_screenId from AIER_SCREEN_INFO where new_screenteamid=@screenteamid) and new_isoperate=1 and CreatedOn >= @startdate and CreatedOn <= @enddate)", new { screenteamid = screenteamid, startdate = startdate, enddate = enddate });
                }
                else if (type == 5)
                {
                    sqlBuilder.Where(" s.leadid in(select new_lead from new_reservation where new_screen in (select distinct new_screenId from AIER_SCREEN_INFO where new_screenteamid=@screenteamid) and new_isoperate=0 and CreatedOn >= @startdate and CreatedOn <= @enddate)", new { screenteamid = screenteamid, startdate = startdate, enddate = enddate });
                }

                if (!string.IsNullOrWhiteSpace(customerName))
                {
                    sqlBuilder.OrWhere(" s.fullname=@fullname ",new { fullname=customerName });
                }
                if (!string.IsNullOrWhiteSpace(phone))
                {
                    sqlBuilder.OrWhere(" s.new_phone=@new_phone ",new { new_phone = phone });
                }
                if (!string.IsNullOrWhiteSpace(sfz))
                {
                    sqlBuilder.OrWhere(" s.new_citizenid=@new_citizenid ", new { new_citizenid =sfz});
                }

                log.Info(string.Format("LeadDal对像中,QueryLeadPageByConditions ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                leadcount = a.ExecuteScalar<int>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });

            return leadcount;
        }

        public int QueryLeadCountByConditions(string customerName, string phone, string sfz,Guid ownerid)
        {
            int leadcount = 0;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select count(*) from leadbase s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where(" s.ownerid=@ownerid "); 
                sqlParams.Add("@ownerid", ownerid);

                if (!string.IsNullOrWhiteSpace(customerName))
                {
                    sqlBuilder.OrWhere(" s.fullname=@fullname ");
                    sqlParams.Add("@fullname", customerName);
                }
                if (!string.IsNullOrWhiteSpace(phone))
                {
                    sqlBuilder.OrWhere(" s.new_phone=@new_phone ");
                    sqlParams.Add("@new_phone", phone);
                }
                if (!string.IsNullOrWhiteSpace(sfz))
                {
                    sqlBuilder.OrWhere(" s.new_citizenid=@new_citizenid ");
                    sqlParams.Add("@new_citizenid", sfz);
                }

                log.Info(string.Format("LeadDal对像中,QueryLeadPageByConditions ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                leadcount = a.ExecuteScalar<int>(sqlTemplate.RawSql, sqlParams);
            });

            return leadcount;
        }

        /// <summary>
        /// 根据客户ID查询客户信息
        /// 查询条件为：客户ID
        /// 原程序 LeadBaseServiceImpl/select
        /// </summary>
        /// <param name="leadId"></param>
        /// <returns></returns>
        public LeadView QueryLeadInfoById(Guid leadid)
        {
            LeadView lead = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from leadbase s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (leadid != Guid.Empty)
                {
                    sqlBuilder.Where(" s.leadid=@leadid ");
                    sqlParams.Add("@leadid", leadid);
                }
                //log.Info(string.Format("LeadDal对像中,QueryLeadInfoById ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                lead = a.QueryFirstOrDefault<LeadView>(sqlTemplate.RawSql, sqlParams);
            });

            return lead;
        }
        /// <summary>
        /// 根据条件查询leadid
        /// 查询条件为：姓名、负责人ID、身份证、手机号码、座机
        /// 原程序 LeadBaseServiceImpl/queryLeadid
        /// </summary>
        /// <param name="customername">姓名</param>
        /// <param name="ownerid">负责人ID</param>
        /// <param name="citizenid">身份证</param>
        /// <param name="iphoneno">手机号码</param>
        /// <param name="telno">座机</param>
        /// <returns></returns>
        public Guid QueryLeadidByConditions(string customername, Guid ownerid, string citizenid, string iphoneno, string telno)
        {
            Guid leadid = Guid.Empty;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select leadid from leadbase s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (!string.IsNullOrWhiteSpace(customername))
                {
                    sqlBuilder.Where(" s.fullname=@fullname ");
                    sqlParams.Add("@fullname", customername);
                }
                if (ownerid != Guid.Empty)
                {
                    sqlBuilder.Where(" s.ownerid=@ownerid ");
                    sqlParams.Add("@ownerid", ownerid);
                }
                if (!string.IsNullOrWhiteSpace(citizenid))
                {
                    sqlBuilder.Where(" s.new_citizenid=@new_citizenid ");
                    sqlParams.Add("@new_citizenid", citizenid);
                }
                if (!string.IsNullOrWhiteSpace(iphoneno))
                {
                    sqlBuilder.Where(" s.new_phone=@new_phone ");
                    sqlParams.Add("@new_phone", iphoneno);
                }
                if (!string.IsNullOrWhiteSpace(telno))
                {
                    sqlBuilder.Where(" s.Telephone2=@Telephone2 ");
                    sqlParams.Add("@Telephone2", telno);
                }
                sqlBuilder.Where(" s.statecode!=2 ");
                //log.Info(string.Format("LeadDal对像中,QueryLeadidByConditions ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                leadid = a.QueryFirstOrDefault<Guid>(sqlTemplate.RawSql, sqlParams);
            });

            return leadid;
        }

        /// <summary>
        /// TODO
        /// 根据班级查询未完成的学生 
        /// 原程序 ScreeningServiceImpl-> queryCustomerInfo(String schoolId, String classNum, List<String> customerids)
        /// 查询有问题，无法按原来逻辑写方法，界面绑定时再写 (lead视图下没有classNum,所以无法按原来的方法查询)
        /// </summary>
        /// <param name="schoolId">学校ID</param>
        /// <param name="classId">学号</param>
        /// <param name="customerids"></param>
        /// <returns></returns>
        //
        public IEnumerable<LeadView> QueryLeadInfoBySchoolIdAndClassNum(string schoolId, string classId,List<string> customerids=null)
        {
            string sql = $@"select  l.LeadId, l.fullname, l.new_class, l.new_age, l.new_gender, l.new_citizenid, l.Address1_Name, l.Address1_Telephone1, l.new_mobile, 
                                    l.EMailAddress1, l.new_qq, l.new_wechat, l.new_alias, l.new_birthdate, l.CreatedOn, l.CreatedBy, l.ModifiedOn,l.ModifiedBy, l.Telephone1,
                                    l.Telephone2, l.new_birthyear, 
                                    l.new_insurancetype, l.new_insurancelocation, l.new_insuranceid, l.new_isformal, l.new_member, l.new_patientsource, l.new_firstsysid, 
                                    l.new_profession, l.new_eyedisease, l.new_eyedisease_instruction, l.new_heartdisease, l.new_heartdisease_times, 
                                    l.new_heartdisease_instruction, l.new_diabetes, l.new_diabetes_times, l.new_diabetes_instruction, l.new_disease, l.new_hypertension, 
                                    l.new_hypertension_times, l.new_hypertension_instruction, l.new_allergy, l.new_allergy_instruction, l.StateCode, 
                                    l.Address1_Telephone2, l.Description, l.new_school, l.new_studentid, l.new_parentname, l.Address1_Telephone3, l.Telephone3 
                                    from Lead l with(nolock)
                                    left join new_sc_customerBase ls with(nolock) on ls.new_customerid = l.LeadId
                                    /**where**/";

             return dapperHelper.ExecueWithoutTransaction(a =>
             {
                 if (!string.IsNullOrEmpty(schoolId) || !string.IsNullOrEmpty(classId))
                 {
                     SqlBuilder sqlBuilder = new SqlBuilder();
                     var sqlTemplate = sqlBuilder.AddTemplate(sql);
                     sqlBuilder.Where("(ls.new_scstate=0 or ls.new_customerid is null ) ");
                     if (!string.IsNullOrEmpty(schoolId))
                     {
                         sqlBuilder.Where(" l.new_school=@schoolId ", new { schoolId });
                     }
                     if(!string.IsNullOrEmpty(classId))
                     {
                         sqlBuilder.Where(" l.new_class=@classId ", new { classId });
                     }
                     //if (scid!=Guid.Empty) {
                     //    sqlBuilder.Where(" ls.new_scid=@scid ", new { scid });
                     //}

                     return a.Query<LeadView>(sqlTemplate.RawSql, sqlTemplate.Parameters);

                 }
                 return null;
             });
        }

        /// <summary>
        /// 根据潜在顾客id查询学生信息
        /// </summary>
        /// <param name="leadid"></param>
        /// <returns></returns>
        public LeadView QueryStudentInfoById(string leadid)
        {
            string sql = $@"select new_schoolName,new_className,class.new_classNumber,sc.new_scnum,school.new_stage 
                                    from Lead l with(nolock)
                                    /**leftjoin**/ /**where**/";

            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.LeftJoin(@"new_sc_customerbase as sc on  sc.new_customerid = l.LeadId 
                                      left join new_schoolbase as school on school.new_schoolId = l.new_school
                                      left join new_classbase as class on class.new_classId = l.new_class");
                sqlBuilder.Where(" LeadId=@leadid ", new { leadid });
                return a.QueryFirstOrDefault<LeadView>(sqlTemplate.RawSql, sqlTemplate.Parameters);

            });
        }


        /// <summary>
        /// 根据leadId获取潜客数据
        /// </summary>
        /// <param name="leadId"></param>
        /// <returns></returns>
        public AccountDetailView GetAcountDetailsData(Guid leadId)
        {
            if (leadId != Guid.Empty)
            {
                return new DapperHelper().ExecueWithoutTransaction(select =>
                {
                    string strsql = @"select leadid,fullname,new_age,case new_gender when 0 then '未知的性别' when 1 then '男' when 2 then '女' end as new_gender,
                                        address1_telephone1 as phone,new_phone,new_citizenid,address1_Name,new_address,
                                        ownerIdName,createdByName,DATEADD ( hour, 8, CreatedOn) as createdOn,
                                        case new_insurancetype 
                                        when 9 then '城镇医保'
                                        when 10 then '小城镇医保'
                                        when 11 then '互助帮困'
                                        when 12 then '居民保险'
                                        when 13 then '中小学生医保'
                                        when 14 then '离休'
                                        when 15 then '伤残'
                                        when 16 then '新三险外来从业人员'
                                        when 1 then '省医保'
                                        when 17 then '精简回乡'
                                        when 2 then '城镇职工医保'
                                        when 3 then '城乡居民医保'
                                        when 4 then '新农合'
                                        when 6 then '铁路保险'
                                        when 7 then '商业保险'
                                        when 8 then '国际保险'
                                        when 5 then '其他'
                                        else '其他'
                                        end as new_insurancetype                                        
                                        from Lead
                                      /**where**/";
                    SqlBuilder sqlBuilder = new SqlBuilder();
                    var sqlTemplate = sqlBuilder.AddTemplate(strsql);
                    sqlBuilder.Where("leadId = @leadId", new { leadId = leadId });
                    return select.QueryFirstOrDefault<AccountDetailView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
                });
            }
            return null;
        }
    }
}
